import random
import pymysql
import demjson
import re
import time
import os
from dbutils.pooled_db import PooledDB
def randomName():
    # 百家姓全部姓氏
    firstName = "赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平" \
                "黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮" \
                "龚程嵇邢滑裴陆荣翁荀羊於惠甄麴家封芮羿储靳汲邴糜松井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫宁仇栾暴甘钭厉戎祖武符刘景詹束龙叶幸司韶郜黎蓟薄印宿白怀蒲邰从鄂索咸籍赖卓蔺屠蒙池乔阴欎胥能苍" \
                "双闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍舄璩桑桂濮牛寿通边扈燕冀郏浦尚农温别庄晏柴瞿阎充慕连茹习宦艾鱼容向古易慎戈廖庾终暨居衡步都耿满弘匡国文寇广禄阙东殴殳沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚那简饶空" \
                "曾毋沙乜养鞠须丰巢关蒯相查後荆红游竺权逯盖益桓公晋楚闫法汝鄢涂钦归海帅缑亢况后有琴梁丘左丘商牟佘佴伯赏南宫墨哈谯笪年爱阳佟言福百家姓终"
    # 百家姓中双姓氏
    firstName2 = "万俟司马上官欧阳夏侯诸葛闻人东方赫连皇甫尉迟公羊澹台公冶宗政濮阳淳于单于太叔申屠公孙仲孙轩辕令狐钟离宇文长孙慕容鲜于闾丘司徒司空亓官司寇仉督子颛孙端木巫马公西漆雕乐正壤驷公良拓跋夹谷宰父谷梁段干百里东郭南门呼延羊舌微生梁丘左丘东门西门南宫南宫"
    # 女孩特征名字
    girl = '秀娟英华慧巧美娜静淑惠珠翠雅芝玉萍红娥玲芬芳燕彩春菊兰凤洁梅琳素云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧璐娅琦晶妍茜秋珊莎锦黛青倩婷姣婉娴瑾颖露瑶怡婵雁蓓纨仪荷丹蓉眉君琴蕊薇菁梦岚苑婕馨瑗琰韵融园艺咏卿聪澜纯毓悦昭冰爽琬茗羽希宁欣飘育滢馥筠柔竹霭凝晓欢霄枫芸菲寒伊亚宜可姬舒影荔枝思丽'
    # 男孩特征名字
    boy = '伟刚勇毅俊峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘'
    # 普通名
    name = '中笑贝凯歌易仁器义礼智信友上都卡被好无九加电金马钰玉忠孝灿杨'
    sex = random.choice(range(2))
    if (random.choice(range(100)) < 20):
        i = random.randrange(0, len(firstName2) - 1, 2)
        first_name = firstName2[i:i+1 + 1]
    else:
        first_name = firstName[random.choice(range(len(firstName)))]

    if (sex == 1):
        if (random.choice(range(3)) == 0):
            last_name = boy[random.choice(range(len(boy)))]
        elif (random.choice(range(3)) == 1):
            last_name = name[random.choice(range(len(name)))]
        else:
            last_name = boy[random.choice(range(len(boy)))]
            last_name += name[random.choice(range(len(name)))]
        name = first_name + last_name + "男"
        return name
    else:
        if (random.choice(range(3)) == 0):
            last_name = girl[random.choice(range(len(girl)))]
        elif (random.choice(range(3)) == 1):
            last_name = name[random.choice(range(len(name)))]
        else:
            last_name = girl[random.choice(range(len(girl)))]
            last_name += name[random.choice(range(len(name)))]
        name = first_name + last_name + "女"
        return name

def randomNo(kind):
    if (kind == "教师"):
        temp1 = random.choice(range(1951, 2021 + 1))
        temp2 = random.choice(range(0, 999 + 1))
        temp3 = random.choice(range(0, 999 + 1))
        NO = "T" + str(temp1) + str(temp2) + str(temp3)
    elif (kind == "学生"):
        temp1 = random.choice(range(2017, 2021 + 1))
        temp2 = random.choice(range(0, 999 + 1))
        temp3 = random.choice(range(0, 999 + 1))
        NO = "S" + str(temp1) + str(temp2) + str(temp3)
    return NO
def loadArea():
    # 打开文件
    fo = open("area.txt", "r+", encoding = 'utf-8')
    print ("正在加载数据请稍后..... ")
    line = fo.read(-1)
    #print ("读取的字符串: %s" % (line)) 
    area_dic = demjson.decode(line)
    # 关闭文件 
    fo.close()
    return area_dic
def randomPhone():
    prelist=["130","131","132","133","134","135","136","137","138","139","147","150","151","152","153","155","156","157","158","159","186","187","188"]
    return random.choice(prelist)+"".join(random.choice("0123456789") for i in range(8))
def randomDate(a1, a2):
    start = time.mktime(a1)    #生成开始时间戳
    end = time.mktime(a2)      #生成结束时间戳

    #随机生成10个日期字符串
    t = random.randint(start,end)    #在开始和结束时间戳中随机取出一个
    date_touple = time.localtime(t)          #将时间戳生成时间元组
    date = time.strftime("%Y-%m-%d",date_touple)  #将时间元组转成格式化字符串（1976-05-21）
    return date

def randomManjor(list_1, major_dic):
    temp_code = list_1[random.choice(range(len(list_1)))]
    major = major_dic.get(temp_code)
    return major
def randomHomeTown(area_dic, list_2):
    area_code = list_2[random.choice(range(len(list_2)))]
    temp_code = area_code
    homeTown = ""
    temp = []
    while True:
        temp.append(area_dic[int(temp_code)][0])
        temp_code = area_dic[int(temp_code)][1]
        if temp_code == 1:
            break
    temp = reversed(temp)
    for i in temp:
        homeTown  += i
    return homeTown
x = open("./x.log")
try:
    text = x.read()
finally:
    x.close()
print(text)
#学生的年龄组
a1 = (1998,1,1,0,0,0,0,0,0)              #设置开始日期时间元组（1976-01-01 00：00：00）
a2 = (2003,12,31,23,59,59,0,0,0)    #设置结束日期时间元组（1990-12-31 23：59：59）
#教师的年龄组
b1 = (1950,1,1,0,0,0,0,0,0)              #设置开始日期时间元组（1976-01-01 00：00：00）
b2 = (1999,12,31,23,59,59,0,0,0)    #设置结束日期时间元组（1990-12-31 23：59：59）
#部门列表
listDept = ["经济学院", "政法学院", "体育学院", "外国语学院", "人文艺术学院", "机电与建筑工程学院", "信息与计算机学院", "医学院", "管理学院", "教育学院"]
deptDic = {"020401":"经济学院", "020301K":"经济学院", "030101K":"政法学院", "040106":"教育学院", "050201":"外国语学院", "080202":"机电与建筑工程学院", "080701":"信息与计算机学院", "080901":"信息与计算机学院", "081001":"机电与建筑工程学院", "101003":"医学院", "100701":"医学院", "101101":"医学院", "120202":"管理学院", "120204":"管理学院", "120801":"管理学院", "120901K":"管理学院", "130502":"人文艺术学院", "130310":"信息与计算机学院"}
courseDic = {"国际金融":"020401", "公司金融":"020401", "经济学":"020401", "投资银行学":"020301K", "金融风险管理":"020301K", "期货投资学":"020301K", "宪法学":"030101K", "法理学":"030101K", "刑法":"030101K", "保教":"040106", "学前教育教学":"040106", "学前儿童数学教育":"040106", "大学英语":"050201", "翻译":"050201", "中西文化差异":"050201", "机械制图及计算机绘图":"080202", "机械设计基础":"080202", "机械制造基础":"080202", "电路理论":"080701", "模拟电子技术":"080701", "数字电子技术":"080701", "网络程序设计":"080901", "java程序设计":"080901", "数据库原理及应用":"080901", "理论力学":"081001", "材料力学":"081001", "结构力学":"081001", "影像设备学":"101003", "医学影像物理":"101003", "影像检查技术":"101003", "天然药物化学":"100701", "药剂学":"100701", "药物分析":"100701", "人体解剖学":"101101", "护理学基础（双语版）":"101101", "护理综合":"101101", "市场营销学":"120202", "消费者行为学":"120202", "市场营销调研":"120202", "基础会计":"120204", "经济学基础":"120204", "统计学基础":"120204", "电子商务管理":"120801", "网络营销":"120801", "电子商务系统网络维护":"120801", "旅游经济学":"120901K", "旅游资源开发与规划":"120901K", "景区经营与管理":"120901K", "色彩":"130502", "素描":"130502", "中外设计艺术史":"130502", "动画概论":"130310", "动画原理":"130310", "动画造型基础":"130310"}
listMajorCode = ["020401", "020301K", "030101K", "040106", "050201", "080202", "080701", "080901", "081001", "101003", "100701", "101101", "120202", "120204", "120801", "120901K", "130502", "130310"]
majorDic = {"020401":"国际经济与贸易","020301K":"金融学","050201":"英语","030101K":"法学","040106":"学前教育","080202":"机械设计制造及其自动化","080701":"电子信息工程","080901":"计算机科学与技术","081001":"土木工程","101003":"医学影像技术","100701":"药学","101101":"护理学","120202":"市场营销","120204":"财务管理","120801":"电子商务","120901K":"旅游管理","130502":"视觉传达设计","130310":"动画"}
# 连接池
pool = PooledDB (
    #链接数据库的库
    pymysql,
    #最大连接数
    100,
    host = '你的host',
    port = 你的端口号,
    user = '你的用户名',
    passwd = '你的密码',
)
try:
    conn = pool.connection()
    #获取游标
    cur = conn.cursor()
    SQL = "SELECT VERSION()"
    #执行sql语句
    cur.execute(SQL)
    print (cur.fetchall())
    conn.close
    print ("数据库连接测试成功！")
except:    
    print ("error：数据库链接错误！")

try:
    area_dic = loadArea()
    line = str(area_dic)
    pattern = re.compile(r'\d+')
    res = re.findall(pattern, line)
    list_1 = []
    #第一次过滤（过滤掉1）
    for i in res:
        if i == "1":
            continue
        else:
            list_1.append(i)

    #第二次过滤（过滤掉重复项）
    list_2 = sorted(set(list_1))
    #第三次过滤（过滤掉省份代码）
    for i in list_2:
        if int(i) % 1000 == 0:
            list_2.remove(i)

    for i in range(0, 101):
        print (randomName())
        print (randomNo("教师"))
        print (randomNo("学生"))
        print (randomHomeTown(area_dic, list_2))
        print (randomManjor(listMajorCode, majorDic))
        print (randomDate(a1,a2))
        print (randomPhone())
    print ("数据生成测试成功！")
except KeyError as err:
    print (err)

while True:
    for i in range(0, 20):
        print("*", end = "")
    print()
    for i in range(0, 20):
        print("*", end = "")
    print ("\n1.查看帮助")
    print ("2.执行插入数据")
    print ("0.退出脚本")
    command = input("")
    if command == "1":
        f = open("readme.txt")
        try:
            text = f.read()
        finally:
            f.close()
            print (text)

    if command == "2":
        DNO = 1
        CNO = 1
        list_SNO = []
        list_Major = []
        n = int(input("请输入生成数据条数："))
        for i in courseDic.keys():
            CName = i
            temp = randomName()
            Ccredit = random.choice(range(1, 4 + 1))
            sql = "REPLACE INTO courseinfo VALUES('"+str(CNO)+"', '"+CName+"', '"+str(Ccredit)+"');"
            conn = pool.connection()
            #获取游标
            cur = conn.cursor()
            #执行sql语句
            cur.execute("use study;")
            cur.execute(sql)
            conn.commit()
            conn.close()
            CNO += 1
        for i in listMajorCode:
            MNo = i
            Mname = majorDic.get(i)
            temp = randomName()
            Msuper = temp[0:len(temp) - 1]
            Mphone = randomPhone()
            Mdept = deptDic.get(i)
            sql = "REPLACE INTO majorinfo VALUES('"+MNo+"', '"+Mname+"', '"+Msuper+"', '"+Mphone+"', '"+Mdept+"');"
            conn = pool.connection()
            #获取游标
            cur = conn.cursor()
            #执行sql语句
            cur.execute("use study;")
            cur.execute(sql)
            conn.commit()
            conn.close()
        for i in listDept:
            DAdmin = randomName()
            sql = "REPLACE INTO departmentinfo VALUES('"+str(DNO)+"', '"+i+"', '"+randomPhone()+"', '"+DAdmin[0:len(DAdmin) - 1]+"');"
            DNO += 1
            conn = pool.connection()
            #获取游标
            cur = conn.cursor()
            #执行sql语句
            cur.execute("use study;")
            cur.execute(sql)
            conn.commit()
            conn.close()
        for i in range(1, n + 1):
            conn = pool.connection()
            #获取游标
            cur = conn.cursor()
            temp1 = randomName()
            temp2 = randomManjor(listMajorCode, majorDic)
            temp3 = randomManjor(listMajorCode, majorDic)
            temp4 = randomName()
            Ssex = temp1[len(temp1) - 1]
            Sname = temp1[0:len(temp1) - 1]
            Tname = temp4[0:len(temp4) - 1]
            Tsex = temp4[len(temp4) - 1]
            SNO = randomNo("学生")
            TNO = randomNo("教师")
            list_SNO.append(SNO)
            list_Major.append(temp2)
            sql_1 = "INSERT INTO studentinfo VALUES ('"+SNO+"', '"+Sname+"', '"+Ssex+"', '"+temp2+"', '"+randomDate(a1, a2)+"', '"+randomHomeTown(area_dic, list_2)+"');"
            if i % 2 == 0:
                cur.execute("use study;")
                sql_2 = "INSERT INTO teacherinfo VALUES ('"+TNO+"', '"+Tname+"', '"+Tsex+"', '"+temp3+"');"
                cur.execute(sql_2)
            #执行sql语句
            cur.execute("use study;")
            cur.execute(sql_1)
            conn.commit()
            conn.close()
        for i in range(0, n):
            SNO = list_SNO[i]
            for k in range(0, 2 + 1):
                course_list = []
                conn = pool.connection()
                #获取游标
                cur = conn.cursor()
                sql_seacher1 = "SELECT TNo FROM teacherinfo WHERE TManjor = '"+list_Major[i]+"';"
                major_code = list(majorDic.keys())[list(majorDic.values()).index(list_Major[i])]
                if major_code in courseDic.values():
                    for a in range(0, len(courseDic)):
                        if list(courseDic.values())[a] == major_code:
                            course_list.append(list(courseDic.keys())[a])

                course_name = course_list[k]
                print (course_list)
                sql_seacher2 = "SELECT CNo FROM courseinfo WHERE CName = '"+course_name+"';"
                cur.execute("use study;")
                cur.execute(sql_seacher1)
                tNO = cur.fetchall()
                conn.commit()
                conn.close

                conn = pool.connection()
                #获取游标
                cur = conn.cursor() 
                cur.execute("use study;")
                cur.execute(sql_seacher2)
                CNO = cur.fetchall()
                #print(CNO)
                CNO = CNO[0][0]
                j = random.choice(range(0, len(tNO)))
                TNO = tNO[j][0]
                Grade = 0
                if (random.choice(range(100)) < 20):
                    Grade = random.choice(range(30, 60 + 1))
                else:
                    Grade = random.choice(range(60, 100 + 1))
                sql_3 = "INSERT INTO coursegrade VALUES ('"+CNO+"', '"+SNO+"', '"+TNO+"', '"+str(Grade)+"');"
                cur.execute("use study;")
                cur.execute(sql_3)
                conn.commit()
                conn.close
        print ("数据插入成功！感谢使用本脚本。")

    if command == "0":
        break
